package com.his.dao.jpa;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import com.his.pojos.jpa.HisDrug;
import com.his.vo.MxjDrugVo;

public interface MxjDrugDAO extends CrudRepository<HisDrug, Long> {
	
	@Query("select "
			+ "new com.his.vo.MxjDrugVo( d.cpNo,  d.cpName,  d.cpType,  d.cpNorms,  pd.ygxqNumber,  d.cpFunction, pd.ygxqSaleprice, pd.yfxqUnit) "
			+ "from HisDrug d,HisPharmacydetail pd "
			+ "where d.kcNo=pd.kcNo order by d.cpNo asc")
	public List<MxjDrugVo> findHisDrugByPage(Pageable pageable);
	
	@Query("select "
			+ "new com.his.vo.MxjDrugVo( d.cpNo,  d.cpName,  d.cpType,  d.cpNorms,  pd.ygxqNumber,  d.cpFunction, pd.ygxqSaleprice, pd.yfxqUnit) "
			+ "from HisDrug d,HisPharmacydetail pd "
			+ "where d.kcNo=pd.kcNo and d.cpType=:cpType")
	 public List<MxjDrugVo> findByPageAndcpType(Pageable pageable,@Param("cpType")String cpType);

	@Query("select count(*) from HisDrug d,HisPharmacydetail pd "
			+ "where d.kcNo=pd.kcNo and d.cpType=:cpType")
	 public String findByPageAndcpTypeSize(@Param("cpType")String cpType);
	
	@Query("select "
			+ "new com.his.vo.MxjDrugVo( d.cpNo,  d.cpName,  d.cpType,  d.cpNorms,  pd.ygxqNumber,  d.cpFunction, pd.ygxqSaleprice, pd.yfxqUnit) "
			+ "from HisDrug d,HisPharmacydetail pd "
			+ "where d.kcNo=pd.kcNo and d.cpName like:cpName")
	 public List<MxjDrugVo> findByPageAndcpNameLike(Pageable pageable,@Param("cpName")String cpName);
	 
	 @Query("select count(*) from HisDrug d,HisPharmacydetail pd "
	 		+ "where d.kcNo=pd.kcNo and d.cpName like:cpName")
	 public String findByNameSize(@Param("cpName")String cpName);
	 
	 @Query("select distinct cpType from HisDrug")
	 public String[] findAllcpType();
	 
	 @Query("select d.cpNo from HisDrug d,HisPharmacydetail pd"
	 		+ " where d.kcNo=pd.kcNo and d.cpName =:cpName")
	 public long findADrugcpNo(@Param("cpName")String cpName);
}
